package jsyf.oa.dao.system;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import jsyf.oa.entity.system.UserAchievementsEntity;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

/**
 * Created by 92852 on 2018/8/2.
 */
@Repository
public class UserAchievementsDao extends BaseDao {

    public UserAchievementsDao() {
        super.iBaseEntity = new UserAchievementsEntity();
    }

    public List<UserAchievementsEntity> getEntityListByCondition(String condition, Object[] params) {
        List<JSONObject> jsonList = getJSONListByCondition(condition, params);
        List<UserAchievementsEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            UserAchievementsEntity entity = (UserAchievementsEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, UserAchievementsEntity.class);
    }

    public List<JSONObject> getAchievementByCompany(String companyId) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n" +
                "region.companyId,\n" +
                "region.companyName,\n" +
                "region.regionId,\n" +
                "region.regionName,\n" +
                "IFNULL(achieve.MANAGER_TARGET_NUM,0) AS managerTargetNum,\n" +
                "IFNULL(achieve.BIG_MANAGER_TARGER_NUM,0) AS bigManagerTargetNum,\n" +
                "IFNULL(user2Region.managerCount,0) AS managerCount,\n" +
                "IFNULL(user2BigRegion.bigManagerCount,0) AS bigManagerCount,\n" +
                "CASE\n" +
                "\t\tWHEN \n" +
                "\t\t\tIFNULL(achieve.BIG_MANAGER_TARGER_NUM,0)-IFNULL(user2BigRegion.bigManagerCount,0) >0\n" +
                "\t\t THEN\n" +
                "\t\t\tIFNULL(achieve.BIG_MANAGER_TARGER_NUM,0)-IFNULL(user2BigRegion.bigManagerCount,0)\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                "AS bigManagerVacant,\n" +
                "CASE\n" +
                "\t\tWHEN \n" +
                "\t\t\tIFNULL(achieve.MANAGER_TARGET_NUM,0)-IFNULL(user2Region.managerCount,0)>0\n" +
                "\t THEN\n" +
                "\t\t\tIFNULL(achieve.MANAGER_TARGET_NUM,0)-IFNULL(user2Region.managerCount,0)\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                " AS managerVacant\n" +
                "FROM(\n" +
                "SELECT\n" +
                "\tt_sys_company.ID AS companyId,\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "\tt_sys_region.ID AS regionId,\n" +
                "\tt_sys_region.`NAME` AS regionName,\n" +
                "\tt_sys_region.`SORT_NO` AS regionSortNo\n" +
                "FROM\n" +
                "\tt_sys_company\n" +
                "LEFT JOIN t_sys_region ON t_sys_company.ID=t_sys_region.COMPANY_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_company.ID = '"+companyId+"'\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                "AND t_sys_region.PARENT_ID=0) AS region\n" +
                "LEFT JOIN t_sys_user_achievements AS achieve  ON region.regionId=achieve.BIG_AREA_ID\n" +
                "LEFT JOIN (\n" +
                "SELECT COUNT(1) AS managerCount,REGION_ID AS regionId,t_sys_user.name FROM t_sys_user_2_region \n" +
                "LEFT JOIN t_sys_user ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE 1=1 AND t_sys_role_name.`NAME`='区域经理'\n" +
                "AND t_sys_user.JOB_STATE!=1\n" +
                "GROUP BY t_sys_user_2_region.REGION_ID\n" +
                ") AS user2Region ON  user2Region.regionId=region.regionId\n" +
                "LEFT JOIN (\n" +
                "SELECT\n" +
                "\t\t\tCOUNT(1) AS bigManagerCount,\n" +
                "\t\tt_sys_depart.`NAME` AS departName\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_part_time_for_report\n" +
                "\t\tLEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "\t\tLEFT JOIN t_sys_depart ON v_sys_part_time_for_report.departId = t_sys_depart.ID\n" +
                "\t\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\t\tLEFT JOIN t_sys_user ON v_sys_part_time_for_report.userId = t_sys_user.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\tv_sys_part_time_for_report.companyId = '"+companyId+"'\n" +
                "\t\tAND v_sys_part_time_for_report.userId IS NOT NULL\n" +
                "\t\tAND t_sys_role_name.`NAME` ='大区经理'\n" +
                "\t\tAND t_sys_depart.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_company.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_role_name.DEL_FLAG != 1\n" +
                "\t\tAND t_sys_user.JOB_STATE != 1\n" +
                "GROUP BY t_sys_depart.ID\n" +
                ") AS user2BigRegion ON   user2BigRegion.`departName`=region.regionName\n" +
                "ORDER BY region.regionSortNo\n ");

        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }
}

